﻿'Description:物料采购类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:20190411
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Imports System.Text.RegularExpressions

Public Class uLMSMaterialPlanBusiness
    Implements IBusiness.uLMSMaterialPlanIBusiness
    Private m_CommonObj As New uLMSCommonBusiness
    ''' <summary>
    ''' 导入采购计划
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="strMsg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function LeadInProcurementPlan(ByRef dt As DataTable, ByVal UserInfo As CurrentUserInfo) As Boolean Implements uLMSMaterialPlanIBusiness.LeadInProcurementPlan
        LeadInProcurementPlan = False
        Dim isTrue = True
        Dim selectSql As String = "select *  from PROCUREMENTPLAN where isuse=0 and PROCUREMENTPLANNO='{0}' and DRAWINGNO='{1}'"
        Dim InsertSql As String = <STRING>INSERT INTO PROCUREMENTPLAN P (Materialcode,DRAWINGNO,PRODUCTNAME,MATERIALTRADEMARK,MATERIALTYPE,procurementqty,
        SINGLEMATERIALPARTNUM,TECHNICALSTANDARD,TECHNICALSTATUS,MATERIALSPECIFICATION,MATERIALSIZE,PRICE,SINGLEQUOTA,
        SINGLESTANDQUOTA,GROSSNUM,RECEIVEPLANDATE,FEEDINGDATE,INVENTORYQTY,ORDERSPECIFICATION,QTY,UOM,PRODUCEDIRECTORPLANNER,MATERIALDIRECTORPLANNER,
        ISCLOSE,ISUSE,CREATEDATE,CREATEEMPLOYEEID,PROCUREMENTPLANNO,PROCUREMENTPLANID,MATERIALNEEDNO)
        VALUES('{0}','{1}','{2}','{3}','{4}',{5},'{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',to_date('{15}','yyyy/mm/dd hh24:mi:ss'),to_date('{16}','yyyy/mm/dd hh24:mi:ss'),'{17}','{18}','',
        '{19}','{20}','{21}',0,0,SYSDATE,'{22}','{23}','{24}','{25}')</STRING>.Value

        Dim SQLStringList As New ArrayList

        For Each row As DataRow In dt.Rows
            Dim validateSql As String = String.Format(selectSql, row("PROCUREMENTPLANNO").ToString, row("DRAWINGNO").ToString)
            If DatabaseHelper.Query_ReturnDatTable(validateSql).Rows.Count > 0 Then
                isTrue = False
                row("strMessage") += (row("PROCUREMENTPLANNO") + "件号：" + row("DRAWINGNO").ToString + ",已有记录")
            End If

            'If m_CommonObj.GetTableInfo("productbase", "productname", row("DRAWINGNO").ToString).Rows.Count = 0 Then '件号
            '    isTrue = False
            '    row("strMessage") += "系统中没有对应件号"
            'End If

            If Not row.IsNull("PRODUCEDIRECTORPLANNER") Then
                If m_CommonObj.GetTableInfo("employee", "fullname", row("PRODUCEDIRECTORPLANNER").ToString).Rows.Count = 0 Then '生产指挥部计划员
                    isTrue = False
                    row("strMessage") += "系统中没有，生产指挥部计划员"
                End If
            End If

            If Not row.IsNull("MATERIALDIRECTORPLANNER") Then
                If m_CommonObj.GetTableInfo("employee", "fullname", row("MATERIALDIRECTORPLANNER").ToString).Rows.Count = 0 Then '物资部计划员
                    isTrue = False
                    row("strMessage") += "系统中没有，物资部计划员"
                End If
            End If

            Dim procurementplanID As String = Guid.NewGuid.ToString
            Dim insertSqlRow As String = String.Format(InsertSql, row("Materialcode").ToString, row("DRAWINGNO").ToString,
                                                       row("PRODUCTNAME").ToString, row("MATERIALTRADEMARK").ToString, row("MATERIALTYPE").ToString,
                                                     row("procurementqty").ToString, row("SINGLEMATERIALPARTNUM").ToString, row("TECHNICALSTANDARD").ToString,
                                                       row("TECHNICALSTATUS").ToString, row("MATERIALSPECIFICATION").ToString, row("MATERIALSIZE").ToString,
                                                       row("PRICE").ToString, row("SINGLEQUOTA").ToString, row("SINGLESTANDQUOTA").ToString,
                                                       row("GROSSNUM").ToString, row("RECEIVEPLANDATE").ToString, row("FEEDINGDATE").ToString,
                                                       row("INVENTORYQTY").ToString, row("ORDERSPECIFICATION").ToString, row("UOM").ToString,
                                                       row("PRODUCEDIRECTORPLANNER").ToString, row("MATERIALDIRECTORPLANNER").ToString,
                                                       UserInfo.EmployeeID, row("ProcurementPlanNo").ToString, procurementplanID, row("MaterialNeedNo").ToString)
            row("ProcurementplanID") = procurementplanID
            SQLStringList.Add(insertSqlRow)
        Next

        If isTrue Then
            DatabaseHelper.ExecuteSqlTran(SQLStringList)
            LeadInProcurementPlan = True
        End If

    End Function

    ''' <summary>
    ''' 导入需求投料计划
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="UserInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function LeadInFeedingDemandPlan(ByRef dt As DataTable, ByVal UserInfo As CurrentUserInfo) As Boolean Implements uLMSMaterialPlanIBusiness.LeadInFeedingDemandPlan
        LeadInFeedingDemandPlan = False
        Dim isTrue = True
        Dim nowYear As Integer = DateTime.Now.Year
        Dim SQLStringList As New ArrayList
        Dim selectSql As String = "SELECT *  FROM FeedingDemandPlan mp WHERE mp.procurementplanno='{0}' AND mp.planmonth='{1}' and mp.DrawingNO='{2}'"
        Dim InsertSql As String = <string>INSERT INTO FeedingDemandPlan (FeedingDemandPlanID,ProcurementPlanNo,MaterialCode,DrawingNO,ProductName,
        PlanMonth,ProductFamily,SingleStandNum,OriginalFactory,UseFactory,CreateDate,CREATEEMPLOYEEID,Qty) VALUES('{0}','{1}','{2}','{3}','{4}','{5}',
        '{6}','{7}','{8}','{9}',SYSDATE,'{10}',{11})</string>
        Dim deleteSql As String = <string>DELETE FeedingDemandPlan t  WHERE t.feedingdemandplanid='{0}'</string>.Value

        Dim monthList As New List(Of String)
        For i As Integer = 0 To dt.Columns.Count - 1
            If dt.Columns(i).ColumnName.Contains("月") Then
                monthList.Add(dt.Columns(i).ColumnName)
            End If
        Next

        If monthList.Count = 0 Then
            Exit Function
        End If

        For Each row As DataRow In dt.Rows
            'If m_CommonObj.GetTableInfo("productbase", "productname", row("DRAWINGNO").ToString).Rows.Count = 0 Then '件号
            '    isTrue = False
            '    row("strMessage") += "系统中没有对应件号"
            '    Continue For
            'End If
            Dim isExit As String = ""
            For Each colName As String In monthList
                If row.IsNull(colName) Then
                    Continue For
                End If
                Dim month As Integer = Val(Regex.Replace(colName, "[^\d.\d]+", ""))
                Dim temYear As Integer = nowYear
                If colName.Contains("上年") Then
                    temYear -= 1
                End If
                Dim validateSql As String = String.Format(selectSql, row("procurementplanno").ToString, temYear.ToString + "/" + month.ToString, row("DRAWINGNO").ToString)
                Dim id As String = Guid.NewGuid.ToString
                Dim validateDt As DataTable = DatabaseHelper.Query_ReturnDatTable(validateSql)
                Dim isInsert As Boolean = True '是否需要插入
                If validateDt.Rows.Count > 0 Then '修改
                    Dim deleteSqlRow As String = String.Format(deleteSql, validateDt.Rows(0)("feedingdemandplanid").ToString)
                    id = validateDt.Rows(0)("feedingdemandplanid").ToString
                    SQLStringList.Add(deleteSqlRow)
                    'If DatabaseHelper.ExecuteSql(deleteSqlRow) < 1 Then
                    '    isInsert = False
                    'Else
                    '    id = validateDt.Rows(0)("feedingdemandplanid").ToString
                    'End If
                    isExit += colName + ","
                End If

                If isInsert Then
                    Dim insertSqlRow As String = String.Format(InsertSql, id, row("ProcurementPlanNo").ToString,
                                                          row("MaterialCode").ToString, row("DrawingNO").ToString, row("ProductName").ToString,
                                                           temYear.ToString + "/" + month.ToString, row("ProductFamily").ToString, row("SingleStandNum").ToString,
                                                           row("OriginalFactory").ToString, row("UseFactory").ToString, UserInfo.EmployeeID, row(colName).ToString)
                    SQLStringList.Add(insertSqlRow)
                End If
       
            Next
            If isExit <> "" Then
                ' row("strMessage") += isExit + "已存在可直接修改"
            End If
        Next

        If isTrue Then
            DatabaseHelper.ExecuteSqlTran(SQLStringList)
            LeadInFeedingDemandPlan = True
        End If


    End Function
    ''' <summary>
    ''' 导入缺件计划
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="UserInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function LeadInMaterialMissingPlan(ByRef dt As DataTable, ByVal UserInfo As CurrentUserInfo) As Boolean Implements uLMSMaterialPlanIBusiness.LeadInMaterialMissingPlan
        LeadInMaterialMissingPlan = False
        Dim isTrue = True

        Dim nowYear As Integer = DateTime.Now.Year
        Dim SQLStringList As New ArrayList
        Dim selectSql As String = "SELECT *  FROM MaterialMissingPlan mp WHERE mp.procurementplanno='{0}' AND mp.planmonth='{1}' and mp.DrawingNO='{2}'"
        Dim InsertSql As String = <string>INSERT INTO MaterialMissingPlan (MaterialMissingPlanID,MaterialCode,ProcurementPlanNo,DrawingNO,ProductName,qty,
        PlanMonth,CreateDate,CREATEEMPLOYEEID) VALUES('{0}','{1}','{2}','{3}','{4}',{5},'{6}',SYSDATE,'{7}')</string>
        Dim deleteSql As String = <string>DELETE MaterialMissingPlan t  WHERE t.MaterialMissingPlanid='{0}'</string>.Value

        Dim monthList As New List(Of String)
        For i As Integer = 0 To dt.Columns.Count - 1
            If dt.Columns(i).ColumnName.Contains("月") Then
                monthList.Add(dt.Columns(i).ColumnName)
            End If
        Next

        If monthList.Count = 0 Then
            Exit Function
        End If

        ' Dim monthPlanQty As DataTable = view.ToTable(False, rowFilterCol)

        For Each row As DataRow In dt.Rows
            'If m_CommonObj.GetTableInfo("productbase", "productname", row("DRAWINGNO").ToString).Rows.Count = 0 Then '件号
            '    isTrue = False
            '    row("strMessage") += "系统中没有对应件号"
            '    Continue For
            'End If
            Dim isExit As String = ""
            For Each colName As String In monthList
                If row.IsNull(colName) Then
                    Continue For
                End If
                Dim month As Integer = Val(Regex.Replace(colName, "[^\d.\d]+", ""))
                Dim temYear As Integer = nowYear
                If colName.Contains("上年") Then
                    temYear -= 1
                End If
                Dim validateSql As String = String.Format(selectSql, row("procurementplanno").ToString, temYear.ToString + "/" + month.ToString, row("DRAWINGNO").ToString)

                Dim id As String = Guid.NewGuid.ToString
                Dim validateDt As DataTable = DatabaseHelper.Query_ReturnDatTable(validateSql)
                Dim isInsert As Boolean = True '是否需要插入

                If DatabaseHelper.Query_ReturnDatTable(validateSql).Rows.Count > 0 Then
                    Dim deleteSqlRow As String = String.Format(deleteSql, validateDt.Rows(0)("MaterialMissingPlanid").ToString)
                    id = validateDt.Rows(0)("MaterialMissingPlanid").ToString
                    SQLStringList.Add(deleteSqlRow)
                    'If DatabaseHelper.ExecuteSql(deleteSqlRow) < 1 Then
                    '    isInsert = False
                    'Else
                    '    id = validateDt.Rows(0)("MaterialMissingPlanid").ToString
                    'End If
                    isExit += colName + ","
                End If

                Dim insertSqlRow As String = String.Format(InsertSql, id, row("MaterialCode").ToString,
                                                           row("ProcurementPlanNo").ToString, row("DrawingNO").ToString, row("ProductName").ToString,
                                                           row(colName).ToString, temYear.ToString + "/" + month.ToString, UserInfo.EmployeeID)
                SQLStringList.Add(insertSqlRow)

            Next
            If isExit <> "" Then
                ' row("strMessage") += isExit + "已存在可直接修改"
            End If
        Next

        If isTrue Then
            DatabaseHelper.ExecuteSqlTran(SQLStringList)
            LeadInMaterialMissingPlan = True
        End If

    End Function

    ''' <summary>
    ''' 获取采购计划
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetProcurementPlan(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSMaterialPlanIBusiness.GetProcurementPlan
        Dim strSql As String = <string>SELECT p.Materialcode,p.DRAWINGNO, PRODUCTNAME,p.MATERIALTRADEMARK,p.MATERIALTYPE,
                        p.SINGLEMATERIALPARTNUM,p.TECHNICALSTANDARD,p.TECHNICALSTATUS,p.MATERIALSPECIFICATION,p.MATERIALSIZE,p.PRICE,
                        p.SINGLEQUOTA,p.SINGLESTANDQUOTA,p.GROSSNUM,p.RECEIVEPLANDATE,p.FEEDINGDATE,p.INVENTORYQTY,p.ORDERSPECIFICATION,p.QTY,p.procurementqty,p.UOM,
                        p.PRODUCEDIRECTORPLANNER,p.MATERIALDIRECTORPLANNER,p.ISCLOSE,p.ISUSE,p.CREATEDATE,p.CREATEEMPLOYEEID,
                        p.PROCUREMENTPLANNO,p.PROCUREMENTPLANID,p.expectarrivaldate,p.ExpectArrivalNote  FROM PROCUREMENTPLAN p where p.isuse=0 </string>.Value

        If QueryCondition.ContainsKey("IsClose") Then
            strSql += String.Format(" and p.IsClose={0}", QueryCondition("IsClose"))
        End If

        If QueryCondition.ContainsKey("MaterialFullName") Then
            '无物资计划员的所有都展示
            If QueryCondition.ContainsKey("ShowNullMaterialFullName") Then
                strSql += String.Format(" and (p.materialdirectorplanner='{0}' or p.materialdirectorplanner is null)", QueryCondition("MaterialFullName"))
            Else
                strSql += String.Format(" and p.materialdirectorplanner='{0}'", QueryCondition("MaterialFullName"))
            End If
            '
        End If

        If QueryCondition.ContainsKey("CloseEmployeeName") Then
            strSql += String.Format(" and (p.materialdirectorplanner='{0}' or p.ProduceDirectorPlanner='{0}')", QueryCondition("CloseEmployeeName"))
        End If

        If QueryCondition.ContainsKey("CreateEmployeeID") Then
            strSql += String.Format(" and p.createemployeeid='{0}'", QueryCondition("CreateEmployeeID"))
        End If
        If QueryCondition.ContainsKey("DrawingNo") Then '零件号
            strSql += String.Format(" and p.drawingno like '%{0}%'", QueryCondition("DrawingNo"))
        End If

        If QueryCondition.ContainsKey("DrawingNos") Then '零件号(多个)
            strSql += " and " + QueryCondition("DrawingNos")
        End If

        If QueryCondition.ContainsKey("MaterialCode") Then '物料编码
            strSql += String.Format(" and p.MaterialCode like '%{0}%'", QueryCondition("MaterialCode"))
        End If

        If QueryCondition.ContainsKey("ProcurementPlanNo") Then '采购计划编号
            strSql += String.Format(" and p.procurementplanno like '%{0}%'", QueryCondition("ProcurementPlanNo"))
        End If

        If QueryCondition.ContainsKey("MaterialTrademark") Then '材料牌号
            strSql += String.Format(" and p.MaterialTrademark like '%{0}%'", QueryCondition("MaterialTrademark"))
        End If

        If QueryCondition.ContainsKey("MaterialSpecification") Then '材料规格
            strSql += String.Format(" and p.MaterialSpecification like '%{0}%'", QueryCondition("MaterialSpecification"))
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = strSql
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)

        If QueryCondition.ContainsKey("ShowPDMData") Then
            If QueryCondition("ShowPDMData") = "1" Then
                For Each row As DataRow In result.DBTable.Rows
                    Dim productDt As DataTable = m_CommonObj.GetProductInfoByName(row("DRAWINGNO").ToString, "")
                    If productDt.Rows.Count = 0 Then
                        Continue For
                    End If
                    row("PRODUCTNAME") = productDt.Rows(0)("description")
                    row("MATERIALTRADEMARK") = productDt.Rows(0)("Materialtrademark")
                    row("MaterialSpecification") = productDt.Rows(0)("MaterialSpecification")
                    row("MATERIALSIZE") = productDt.Rows(0)("MATERIALSIZE")
                Next
                result.DBTable.AcceptChanges()
            End If
        End If
        GetProcurementPlan = result
    End Function
    ''' <summary>
    ''' 获取采购计划
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetProcurementPlanDt(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable Implements uLMSMaterialPlanIBusiness.GetProcurementPlanDt
        Dim strSql As String = <string>SELECT p.Materialcode,p.DRAWINGNO, PRODUCTNAME,p.MATERIALTRADEMARK,p.MATERIALTYPE,
                        p.SINGLEMATERIALPARTNUM,p.TECHNICALSTANDARD,p.TECHNICALSTATUS,p.MATERIALSPECIFICATION,p.MATERIALSIZE,p.PRICE,
                        p.SINGLEQUOTA,p.SINGLESTANDQUOTA,p.GROSSNUM,p.RECEIVEPLANDATE,p.FEEDINGDATE,p.INVENTORYQTY,p.ORDERSPECIFICATION,p.QTY,p.procurementqty,p.UOM,
                        p.PRODUCEDIRECTORPLANNER,p.MATERIALDIRECTORPLANNER,p.ISCLOSE,p.ISUSE,p.CREATEDATE,p.CREATEEMPLOYEEID,
                        p.PROCUREMENTPLANNO,p.PROCUREMENTPLANID,p.expectarrivaldate,p.ExpectArrivalNote  FROM PROCUREMENTPLAN p where p.isuse=0 </string>.Value

        If QueryCondition.ContainsKey("IsClose") Then
            strSql += String.Format(" and p.IsClose={0}", QueryCondition("IsClose"))
        End If

        If QueryCondition.ContainsKey("MaterialFullName") Then
            strSql += String.Format(" and p.materialdirectorplanner='{0}'", QueryCondition("MaterialFullName"))
        End If

        If QueryCondition.ContainsKey("CreateEmployeeID") Then
            strSql += String.Format(" and p.createemployeeid='{0}'", QueryCondition("CreateEmployeeID"))
        End If
        If QueryCondition.ContainsKey("DrawingNo") Then '零件号
            strSql += String.Format(" and p.drawingno like '%{0}%'", QueryCondition("DrawingNo"))
        End If
        If QueryCondition.ContainsKey("MaterialCode") Then '物料编码
            strSql += String.Format(" and p.MaterialCode like '%{0}%'", QueryCondition("MaterialCode"))
        End If

        If QueryCondition.ContainsKey("ProcurementPlanNo") Then '采购计划编号
            strSql += String.Format(" and p.procurementplanno like '%{0}%'", QueryCondition("ProcurementPlanNo"))
        End If

        If QueryCondition.ContainsKey("DrawingNos") Then '零件号(多个)
            strSql += " and " + QueryCondition("DrawingNos")
        End If

        If QueryCondition.ContainsKey("MaterialTrademark") Then '材料牌号
            strSql += String.Format(" and p.MaterialTrademark like '%{0}%'", QueryCondition("MaterialTrademark"))
        End If

        If QueryCondition.ContainsKey("MaterialSpecification") Then '材料规格
            strSql += String.Format(" and p.MaterialSpecification like '%{0}%'", QueryCondition("MaterialSpecification"))
        End If


        Dim result As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)

        If QueryCondition.ContainsKey("ShowPDMData") Then
            If QueryCondition("ShowPDMData") = "1" Then
                For Each row As DataRow In result.Rows
                    Dim productDt As DataTable = m_CommonObj.GetProductInfoByName(row("DRAWINGNO").ToString, "")
                    If productDt.Rows.Count = 0 Then
                        Continue For
                    End If
                    row("PRODUCTNAME") = productDt.Rows(0)("description")
                    row("MATERIALTRADEMARK") = productDt.Rows(0)("Materialtrademark")
                    row("MaterialSpecification") = productDt.Rows(0)("MaterialSpecification")
                    row("MATERIALSIZE") = productDt.Rows(0)("MATERIALSIZE")
                Next
                result.AcceptChanges()
            End If
        End If
        GetProcurementPlanDt = result
    End Function

    ''' <summary>
    ''' 更新预计到货时间
    ''' </summary>
    ''' <param name="id"></param>
    ''' <param name="expectDate"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateExpextDate(ByVal id As String, ByVal expectDate As String, ByVal expectNote As String) As Boolean Implements uLMSMaterialPlanIBusiness.UpdateExpextDate
        UpdateExpextDate = False

        Dim strSql As String = String.Format("UPDATE PROCUREMENTPLAN p SET p.EXPECTARRIVALDATE=to_date('{0}','yyyy/mm/dd'),p.ExpectArrivalNote='{1}' WHERE p.isuse=0 AND  p.procurementplanid='{2}'",
                                             expectDate, expectNote, id)

        If DatabaseHelper.ExecuteSql(strSql) <= 0 Then
            Exit Function
        End If


        UpdateExpextDate = True
    End Function

    ''' <summary>
    ''' 获取最大批次号
    ''' </summary>
    ''' <param name="materialName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMaxMaterialBatch(ByVal materialName As String) As Integer Implements uLMSMaterialPlanIBusiness.GetMaxMaterialBatch
        Dim strSql As String = String.Format("select *  from MaterialBatch m where m.isuse=0 and m.MaterailBatchName like '{0}/物%'", materialName)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Dim maxBatch As Integer = Val(DateTime.Now.Year.ToString.Substring(1) + "01")
        For Each row In dt.Rows
            Dim rowBatch As Integer = Val(row("MaterailBatchName").ToString.Replace(materialName + "/物", ""))
            If maxBatch > rowBatch Then
                Continue For
            End If
            maxBatch = rowBatch + 1
        Next
        Return maxBatch
    End Function

    ''' <summary>
    ''' 获取最大批次号
    ''' </summary>
    ''' <param name="materialName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMaxMaterialBatch(ByVal materialName As String, ByVal batchType As String) As Integer Implements uLMSMaterialPlanIBusiness.GetMaxMaterialBatch
        Dim strSql As String = String.Format("select *  from MaterialBatch m where m.isuse=0 and m.MaterailBatchName like '{0}/{1}%'", materialName, batchType)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Dim maxBatch As Integer = Val(DateTime.Now.Year.ToString.Substring(1) + "01")
        For Each row In dt.Rows
            Dim rowBatch As Integer = Val(row("MaterailBatchName").ToString.Replace(materialName + "/" + batchType + "", ""))
            If maxBatch > rowBatch Then
                Continue For
            End If
            maxBatch = rowBatch + 1
        Next
        Return maxBatch
    End Function

    ''' <summary>
    ''' 插入批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InserMaterialBatch(ByVal para As Dictionary(Of String, String), ByRef strMsg As String) As Boolean Implements uLMSMaterialPlanIBusiness.InserMaterialBatch
        InserMaterialBatch = False
        Dim selectSql As String = String.Format("select *  from MATERIALBATCH mt where mt.isuse=0 and mt.MATERAILBATCHNAME='{0}'", para("MaterailBatchName"))
        If DatabaseHelper.Query_ReturnDatTable(selectSql).Rows.Count > 0 Then
            If strMsg IsNot Nothing Then
                strMsg = para("MaterailBatchName") + "已存在"
            End If
            Exit Function
        End If
        Dim insertSql As String = ""
        If para.ContainsKey("Initial") Then '初始库存批次
            insertSql = <string>INSERT INTO MATERIALBATCH (MATERAILBATCHID,MATERAILBATCHNAME,SUPPLYBATCH,QTY,DEMANDCHECKDATE,CHECKNO,
            PROCUREMENTPLANID,CREATEDATE,CREATEEMPLOYEEID,ISPHYSICALCHEMICALCHECK,isuse,CheckDate,Checker,PassQty,type) 
            VALUES('{0}','{1}','{2}',{3},to_date('{4}','yyyy/mm/dd'),'{5}','{6}',sysdate,'{7}','{8}',0,sysdate,'{9}',{3},0)</string>.Value
            insertSql = String.Format(insertSql, Guid.NewGuid().ToString, para("MaterailBatchName"), para("SupplyBatch"), para("Qty"), para("DemandCheckDate"),
                                      para("CheckNo"), para("ProcurementplanID"), para("CreateEmployeeID"), para("IsPhysicalChemicalCheck"), para("CreateEmployeeID"))
        Else
            insertSql = <string>INSERT INTO MATERIALBATCH (MATERAILBATCHID,MATERAILBATCHNAME,SUPPLYBATCH,QTY,DEMANDCHECKDATE,CHECKNO,
            PROCUREMENTPLANID,CREATEDATE,CREATEEMPLOYEEID,ISPHYSICALCHEMICALCHECK,isuse,type) VALUES('{0}','{1}','{2}','{3}',to_date('{4}','yyyy/mm/dd'),'{5}','{6}',sysdate,'{7}','{8}',0,1)</string>.Value
            insertSql = String.Format(insertSql, Guid.NewGuid().ToString, para("MaterailBatchName"), para("SupplyBatch"), para("Qty"), para("DemandCheckDate"),
                                      para("CheckNo"), para("ProcurementplanID"), para("CreateEmployeeID"), para("IsPhysicalChemicalCheck"))
        End If
       


        If DatabaseHelper.ExecuteSql(insertSql) <= 0 Then
            Exit Function
        End If
        InserMaterialBatch = True
    End Function
    ''' <summary>
    ''' 获取物料批次（传参key必须为表的字段）
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMaterialBatch(ByVal para As Dictionary(Of String, Object)) As DataTable Implements uLMSMaterialPlanIBusiness.GetMaterialBatch
        Dim strSql As String = "SELECT decode(IsPhysicalChemicalCheck,'0','入厂检验','1','入厂复验') CheckTypeName,m.*  FROM MATERIALBATCH m where m.isuse=0 "
        For Each key As String In para.Keys
            If para(key).GetType.ToString() = "System.String" Then
                If String.IsNullOrWhiteSpace(para(key)) Then
                    strSql += String.Format(" and m.{0} is null", key)
                Else
                    strSql += String.Format(" and m.{0}='{1}'", key, para(key))
                End If
            End If
        Next
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取待检验的物料批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMaterialBatchCheck(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSMaterialPlanIBusiness.GetMaterialBatchCheck
        Dim strSql As String = <string>SELECT decode(IsPhysicalChemicalCheck,'0','入厂检验','1','入厂复验') CheckTypeName,pp.procurementplanno,pp.materialcode,pp.drawingno,pp.productname,pp.MATERIALTRADEMARK,
                                pp.MaterialSpecification,pp.MATERIALSIZE,pp.ProcurementQty,pp.TechnicalStandard,pp.TechnicalStatus,pp.Createdate PROCUREMENTPLANCreatedate,m.*  FROM materialbatch m
                                LEFT JOIN proCUREMENTPLAN pp ON pp.procurementplanid=m.procurementplanid and pp.isuse=0
                                WHERE m.isuse=0 and m.type=1
                             </string>
        If para.ContainsKey("Status") Then 'and m.checkdate IS NULL
            Select Case para("Status")
                Case "0" '入厂待检验
                    strSql += " and m.IsPhysicalChemicalCheck is null and m.checkdate IS NULL"
                Case "2" '待理化提交
                    strSql += " and m.IsPhysicalChemicalCheck=1 and m.ConfirmPhyCheDate is null"
                Case "1" '入厂待复验
                    strSql += " and m.IsPhysicalChemicalCheck=1 and m.ConfirmPhyCheDate is not null and m.EchoCheckDate IS  NULL "
            End Select
        End If

        If para.ContainsKey("DrawingNo") Then '零件号
            strSql += String.Format(" and pp.drawingno like '%{0}%'", para("DrawingNo"))
        End If
        If para.ContainsKey("DrawingNos") Then '零件号(多个)
            strSql += " and " + para("DrawingNos")
        End If
        If para.ContainsKey("CheckNo") Then '检验编号
            strSql += String.Format(" and m.CheckNo like '%{0}%'", para("CheckNo"))
        End If
        If para.ContainsKey("ProcurementPlanNo") Then '采购计划编号
            strSql += String.Format(" and pp.procurementplanno like '%{0}%'", para("ProcurementPlanNo"))
        End If

        If para.ContainsKey("MaterialTrademark") Then '材料牌号
            strSql += String.Format(" and pp.MaterialTrademark like '%{0}%'", para("MaterialTrademark"))
        End If

        If para.ContainsKey("MaterialSpecification") Then '材料规格
            strSql += String.Format(" and pp.MaterialSpecification like '%{0}%'", para("MaterialSpecification"))
        End If

        strSql += " order by m.CREATEDATE asc "

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        Dim result As uMESProcessResult = DatabaseHelper.RunProcedureForPage(paraPage)

        If para.ContainsKey("ShowPDMData") Then
            If para("ShowPDMData") = "1" Then
                For Each row As DataRow In result.DBTable.Rows
                    Dim productDt As DataTable = m_CommonObj.GetProductInfoByName(row("DRAWINGNO").ToString, "")
                    If productDt.Rows.Count = 0 Then
                        Continue For
                    End If
                    row("PRODUCTNAME") = productDt.Rows(0)("description")
                    row("MATERIALTRADEMARK") = productDt.Rows(0)("Materialtrademark")
                    row("MaterialSpecification") = productDt.Rows(0)("MaterialSpecification")
                    row("MATERIALSIZE") = productDt.Rows(0)("MATERIALSIZE")
                Next
                result.DBTable.AcceptChanges()
            End If
        End If
        GetMaterialBatchCheck = result
    End Function

    ''' <summary>
    ''' 保存检验数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SaveCheckInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSMaterialPlanIBusiness.SaveCheckInfo
        SaveCheckInfo = False

        Dim strSql As String = ""
        If para("type") = "0" Then '入厂检验
            strSql = String.Format("UPDATE materialbatch m SET m.IsPhysicalChemicalCheck=0,m.passqty={0} ,m.checkdate=SYSDATE,m.checker='{1}',m.ISTRANSFER=0 WHERE m.materailbatchid='{2}'",
                                            para("PassQty"), para("Checker"), para("MaterailBatchID"))
        ElseIf para("type") = "1" Then '入厂复验
            strSql = String.Format("UPDATE materialbatch m SET m.passqty={0} ,m.EchoCheckDate=SYSDATE,m.EchoChecker='{1}',m.ISTRANSFER=0 WHERE m.materailbatchid='{2}'",
                                            para("PassQty"), para("Checker"), para("MaterailBatchID"))
        ElseIf para("type") = "2" Then '理化提交
            strSql = String.Format("UPDATE materialbatch m SET m.ConfirmPhyCheDate=SYSDATE,m.ConfirmPhyCheEmployeeID='{0}' WHERE m.materailbatchid='{1}'",
                                             para("Checker"), para("MaterailBatchID"))
        ElseIf para("type") = "3" Then '送理化
            strSql = String.Format("UPDATE materialbatch m SET m.IsPhysicalChemicalCheck=1,m.checkdate=SYSDATE,m.checker='{0}' WHERE m.materailbatchid='{1}'",
                                           para("Checker"), para("MaterailBatchID"))
        End If
       
        If DatabaseHelper.ExecuteSql(strSql) <= 0 Then
            Exit Function
        End If

        If para("type") = "2" Then
            SaveCheckInfo = True
            Exit Function
        End If

        If para.ContainsKey("ProcurementPlanID") Then '更新采购计划合格数
            'strSql = String.Format("UPDATE PROCUREMENTPLAN p SET p.qty=nvl(p.qty,0)+{0} WHERE   p.procurementplanid='{1}'", para("PassQty"), para("ProcurementPlanID"))
            strSql = String.Format("UPDATE PROCUREMENTPLAN p SET p.qty=(SELECT SUM(m.passqty) FROM materialbatch m WHERE m.isuse=0 AND m.type=1 AND m.procurementplanid='{0}') WHERE   p.procurementplanid='{0}'", para("ProcurementPlanID"))
            If DatabaseHelper.ExecuteSql(strSql) > 0 Then '关闭已合格完的采购计划
                strSql = String.Format("UPDATE PROCUREMENTPLAN p SET p.isclose=1 WHERE   p.procurementplanid='{0}' and p.qty>=p.ProcurementQty", para("ProcurementPlanID"))
                DatabaseHelper.ExecuteSql(strSql)
            End If
        End If

        SaveCheckInfo = True
    End Function

    ''' <summary>
    ''' 更新采购计划某些信息根据某些条件
    ''' </summary>
    ''' <param name="updatePara"></param>
    ''' <param name="conditionPara"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateTableByField(ByVal tableName As String, ByVal updatePara As Dictionary(Of String, Object), ByVal conditionPara As Dictionary(Of String, String)) As Boolean Implements uLMSMaterialPlanIBusiness.UpdateTableByField
        UpdateTableByField = False
        If updatePara.Count = 0 Then
            Exit Function
        End If
        If conditionPara.Count = 0 Then
            Exit Function
        End If

        Dim strSql As String = "UPDATE " + tableName + " SET "
        Dim n = 0
        For Each updateKey As String In updatePara.Keys
            If n = 0 Then
                If updatePara(updateKey).GetType.ToString() = "System.DateTime" Then
                    strSql += updateKey + "=to_date('" + Date.Parse(updatePara(updateKey)).ToString("yyyy/MM/dd") + "','yyyy/MM/dd')" 'HH:mm:ss
                Else
                    strSql += updateKey + "=" + updatePara(updateKey) + ""
                End If
            Else
                If updatePara(updateKey).GetType.ToString() = "System.DateTime" Then
                    strSql += "," + updateKey + "=to_date('" + Date.Parse(updatePara(updateKey)).ToString("yyyy/MM/dd") + "','yyyy/MM/dd')" 'HH:mm:ss
                Else
                    strSql += "," + updateKey + "=" + updatePara(updateKey) + ""
                End If
            End If
            n += 1
        Next
        n = 0
        For Each conditionKey As String In conditionPara.Keys
            Dim rowCondition As String = ""
            If String.IsNullOrWhiteSpace(conditionPara(conditionKey)) Or conditionPara(conditionKey) = "''" Then
                rowCondition = conditionKey + " is null "
            Else
                rowCondition = conditionKey + "=" + conditionPara(conditionKey)
            End If
            If n = 0 Then
                strSql += " where " + rowCondition
            Else
                strSql += " and " + rowCondition
            End If
            n += 1
        Next
        If Not DatabaseHelper.ExecuteSql(strSql) > 0 Then
            Exit Function
        End If
        UpdateTableByField = True
    End Function

    ''' <summary>
    ''' 查询表数据根据某些条件
    ''' </summary>
    ''' <param name="tableName"></param>
    ''' <param name="conditionPara"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTableInfoByField(ByVal tableName As String, ByVal conditionPara As Dictionary(Of String, String), ByVal conditionLikePara As Dictionary(Of String, String)) As DataTable Implements uLMSMaterialPlanIBusiness.GetTableInfoByField
        Dim selectSql As String = "select * from " + tableName + " where 1=1 "
        If conditionPara IsNot Nothing Then
            For Each conditionKey As String In conditionPara.Keys
                If String.IsNullOrWhiteSpace(conditionPara(conditionKey)) Or conditionPara(conditionKey) = "''" Then
                    selectSql += " and " + conditionKey + " is null "
                Else
                    selectSql += " and " + conditionKey + "=" + conditionPara(conditionKey)
                End If
            Next
        End If
        If conditionLikePara IsNot Nothing Then
            For Each conditionLikeKey As String In conditionLikePara.Keys
                If String.IsNullOrWhiteSpace(conditionLikePara(conditionLikeKey)) Or conditionLikePara(conditionLikeKey) = "''" Then
                    selectSql += " and " + conditionLikeKey + " is null "
                Else
                    selectSql += " and " + conditionLikeKey + " like '%" + conditionLikePara(conditionLikeKey) + "%'"
                End If
            Next
        End If
       
        Return DatabaseHelper.Query_ReturnDatTable(selectSql)
    End Function

    ''' <summary>
    ''' 插入物料领用信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertMaterialReceipt(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSMaterialPlanIBusiness.InsertMaterialReceipt
        Dim insertSql As String = <string>insert into MaterialReceipt(MaterialReceiptID,ApplicationQty,DemandSendDate,Applicant,
        ApplicationDate,FactoryID,DrawingNO) values('{0}',{1},to_date('{2}','yyyy/mm/dd hh24:mi:ss'),'{3}',sysdate,'{4}','{5}')</string>.Value
        insertSql = String.Format(insertSql, para("ID"), para("ApplicationQty"), para("DemandSendDate"), para("Applicant"), para("FactoryID"), para("DrawingNo"))

        Dim i = DatabaseHelper.ExecuteSql(insertSql)
        If i > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    ''' <summary>
    ''' 插入物料批次领用信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Sub InsertMaterialReceiptDetail(ByVal ListPara As List(Of Dictionary(Of String, String))) Implements uLMSMaterialPlanIBusiness.InsertMaterialReceiptDetail

        Dim SQLStringList As New ArrayList

        For Each para In ListPara
            If para.ContainsKey("Sender") Then
                Dim insertSql As String = <string>UPDATE MaterialReceipt t SET t.senddate=SYSDATE,t.sender='{0}',t.sendqty={1} WHERE t.materialreceiptid='{2}'</string>.Value
                insertSql = String.Format(insertSql, para("Sender"), para("SendQty"), para("MaterialReceiptID"))
                SQLStringList.Add(insertSql)
            Else
                Dim insertSql As String = <string>insert into MaterialReceiptDetail(MaterialReceiptID,FactSendQty,MaterailBatchID,MaterailBatchName) 
                values('{0}',{1},'{2}','{3}')</string>.Value
                insertSql = String.Format(insertSql, para("MaterialReceiptID"), para("FactSendQty"), para("MaterailBatchID"), para("MaterailBatchName"))
                SQLStringList.Add(insertSql)
            End If
           
        Next

        DatabaseHelper.ExecuteSqlTran(SQLStringList)

    End Sub

    ''' <summary>
    ''' 获取已申请待发料的领料信息
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetApplyedMaterialReceipt(ByVal QueryCondition As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSMaterialPlanIBusiness.GetApplyedMaterialReceipt
        Dim strSql As String = ""
        If QueryCondition.ContainsKey("ShowPDMData") Then
            If QueryCondition("ShowPDMData") = "1" Then
                strSql = <string>SELECT p.materialtrademark,p.materialspecification,p.description,p.materialsize,fa.factoryname,p.MaterialCode productMaterialCode,null MaterialCode,mp.*  FROM MaterialReceipt mp 
                                LEFT JOIN productbase pb ON pb.productname=mp.drawingno
                                LEFT JOIN product p ON p.productid=pb.revofrcdid
                                left join factory fa on fa.factoryid=mp.factoryid
                                WHERE mp.senddate IS NULL</string>
            Else
                strSql = <string>SELECT p.materialtrademark,p.materialspecification,p.productname description,p.materialsize,fa.factoryname,p.MaterialCode, 
																p.TechnicalStandard,p.TechnicalStatus,mp.*  FROM MaterialReceipt mp 
																LEFT JOIN procurementplan p ON p.drawingno=mp.drawingno
                                left join factory fa on fa.factoryid=mp.factoryid
                                WHERE mp.senddate IS NULL</string>
            End If
        Else
            strSql = <string>SELECT p.materialtrademark,p.materialspecification,p.productname description,p.materialsize,fa.factoryname,p.MaterialCode, 
																p.TechnicalStandard,p.TechnicalStatus,mp.*  FROM MaterialReceipt mp 
																LEFT JOIN procurementplan p ON p.drawingno=mp.drawingno
                                left join factory fa on fa.factoryid=mp.factoryid
                                WHERE mp.senddate IS NULL</string>
        End If
       
        If QueryCondition.ContainsKey("DrawingNo") Then '零件号
            strSql += String.Format(" and mp.drawingno like '%{0}%'", QueryCondition("DrawingNo"))
        End If
        If QueryCondition.ContainsKey("MaterialCode") Then '物料编码
            strSql += String.Format(" and p.MaterialCode like '%{0}%'", QueryCondition("MaterialCode"))
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = strSql
        Return DatabaseHelper.RunProcedureForPage(paraPage)

    End Function

    ''' <summary>
    ''' 根据件号属性查询默认件号信息
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetProductByAttibute(ByVal QueryCondition As Dictionary(Of String, String)) As DataTable Implements uLMSMaterialPlanIBusiness.GetProductByAttibute
        Dim strSql As String = <string>SELECT pb.*,p.*  FROM product p
LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid AND pb.revofrcdid=p.productid where 1=1
                             </string>
        'If QueryCondition.Keys.Count = 0 Then
        '    Return Nothing
        'End If
        If QueryCondition IsNot Nothing Then
            For Each conditionLikeKey As String In QueryCondition.Keys
                If String.IsNullOrWhiteSpace(QueryCondition(conditionLikeKey)) Or QueryCondition(conditionLikeKey) = "''" Then
                    strSql += " and " + conditionLikeKey + " is null "
                Else
                    strSql += " and " + conditionLikeKey + " like '%" + QueryCondition(conditionLikeKey) + "%'"
                End If
            Next
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 物料计划修改记录
    ''' </summary>
    ''' <param name="addData"></param>
    ''' <param name="strMsg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AddMaterialplanTableDataRecord(ByVal addData As Dictionary(Of Dictionary(Of String, String), List(Of Dictionary(Of String, String))), ByRef strMsg As String) As Boolean Implements uLMSMaterialPlanIBusiness.AddMaterialplanTableDataRecord
        AddMaterialplanTableDataRecord = False

        If addData Is Nothing Then
            Exit Function
        End If

        Dim SQLStringList As New ArrayList
        Dim insertMainrecord As String = <string>INSERT INTO  materialplanTablerecord mt (mt.materialplantablerecordid,mt.recorddate,mt.tablename,mt.type,mt.recordemployeeid,mt.TABLEID)
        VALUES('{0}',SYSDATE,'{1}','{2}','{3}','{4}')</string>.Value
        Dim insertMainrecordDetail As String = <string>INSERT INTO  materialplanTablerecorddetail mtd (mtd.materialplantablerecordid,mtd.filedname,mtd.oldvalue,mtd.newvalue)
        VALUES('{0}','{1}','{2}','{3}')</string>.Value
        Dim selectValue As String = <string>select {0} from {1} where {1}ID='{2}'</string>

        For Each mainRecord As Dictionary(Of String, String) In addData.Keys
            If mainRecord Is Nothing Or addData(mainRecord) Is Nothing Then
                Continue For
            End If
            Dim insertMainrecordRow As String = String.Format(insertMainrecord, mainRecord("MaterialPlanTablerecordID"), mainRecord("TablName"), mainRecord("Type"), mainRecord("RecordEmployeeID"), mainRecord("TableID"))
            SQLStringList.Add(insertMainrecordRow)

            For Each mainRecordDetail As Dictionary(Of String, String) In addData(mainRecord)
                If mainRecord("MaterialPlanTablerecordID") <> mainRecordDetail("MaterialPlanTablerecordID") Then '主记录与明细记录MaterialPlanTablerecordID不一致
                    If strMsg IsNot Nothing Then
                        strMsg = "主记录与明细记录MaterialPlanTablerecordID不一致"
                    End If
                    Exit Function
                End If
                'Dim selectValueRow As String = String.Format(selectValue, mainRecordDetail("FiledName"), mainRecord("TablName"), mainRecord("TableID"))
                'mainRecordDetail("OldValue") = DatabaseHelper.GetSingle(selectValueRow).ToString
                mainRecordDetail("NewValue") = mainRecordDetail("NewValue").Replace("'", "")
                Dim insertMainrecordDetailRow As String = String.Format(insertMainrecordDetail, mainRecordDetail("MaterialPlanTablerecordID"), mainRecordDetail("FiledName"), mainRecordDetail("OldValue"), mainRecordDetail("NewValue"))
                SQLStringList.Add(insertMainrecordDetailRow)
            Next
        Next
        DatabaseHelper.ExecuteSqlTran(SQLStringList)
        AddMaterialplanTableDataRecord = True
    End Function

    ''' <summary>
    ''' 根据条件查询投料需求计划,及对应的总入库数
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetFeedingdemandplan(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSMaterialPlanIBusiness.GetFeedingdemandplan
        Dim strSql As String = <string>SELECT a.stockQty,to_date(fd.planmonth,'yyyy/mm') planmonthDate,fd.* FROM feedingdemandplan fd 
        LEFT JOIN (
        SELECT nvl(SUM(m.passqty),0) stockQty,p.drawingno,p.procurementplanno  FROM MATERIALBATCH m 
        LEFT JOIN procurementplan P ON p.procurementplanid=m.procurementplanid
        WHERE m.type=1 
        GROUP BY p.drawingno,p.procurementplanno
        ) a ON a.drawingno=fd.drawingno AND a.procurementplanno=fd.procurementplanno
        WHERE fd.qty>0
        </string>
        If para.ContainsKey("DrawingNos") Then
            strSql += String.Format(" and fd.drawingno in ({0})", para("DrawingNos"))
        End If

        If para.ContainsKey("ProcurementplanNos") Then
            strSql += String.Format(" and fd.procurementplanno in ({0})", para("ProcurementplanNos"))
        End If
        strSql += " ORDER BY to_date(fd.planmonth,'yyyy/mm') ASC"

        Return DatabaseHelper.Query_ReturnDatTable(strSql)

    End Function

    ''' <summary>
    ''' 查询指定件号和采购计划的到料数情况(原材料)
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetFeedingArrivalInfo(ByVal drawingno As String, ByVal procurementplanno As String) As DataTable Implements uLMSMaterialPlanIBusiness.GetFeedingArrivalInfo
        Dim strSql As String = <string>SELECT nvl(a.stockQty,0) stockQty,0 PassQty,to_date(fd.planmonth,'yyyy/mm') planmonthDate,NVL2(a.singlequota,a.singlequota*fd.qty,fd.qty) needqty,fd.* FROM feedingdemandplan fd 
        LEFT JOIN (
        SELECT nvl(SUM(m.passqty),0) stockQty,p.drawingno,p.procurementplanno,p.singlequota  FROM MATERIALBATCH m 
        LEFT JOIN procurementplan P ON p.procurementplanid=m.procurementplanid
        WHERE m.isuse=0 and p.isuse=0
        GROUP BY p.drawingno,p.procurementplanno,p.singlequota
        ) a ON a.drawingno=fd.drawingno AND a.procurementplanno=fd.procurementplanno
        WHERE fd.qty>0
        </string> ' m.type=1
        strSql += String.Format(" and fd.drawingno ='{0}'", drawingno)
        strSql += String.Format(" and fd.procurementplanno ='{0}'", procurementplanno)
        strSql += " ORDER BY to_date(fd.planmonth,'yyyy/mm') ASC"

        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)

        If dt.Rows.Count = 0 Then
            Return dt
        End If

        Dim alreadySumPassQty As Double = 0 '已到料总数
        Dim feedSumqty As Double = 0 '需求总数

        For Each row As DataRow In dt.Rows
            feedSumqty += Val(row("needqty").ToString)
            If feedSumqty <= row("stockQty") Then '需求数小于等于库存数
                row("PassQty") = row("needqty")
                alreadySumPassQty += Val(row("needqty").ToString)
            Else '需求数大于库存数
                If alreadySumPassQty < row("stockQty") Then
                    row("PassQty") = row("stockQty") - alreadySumPassQty
                    alreadySumPassQty = row("stockQty")
                End If
            End If
        Next

        Return dt
    End Function
End Class
